/*
*  Copyright 2019-2020 Zheng Jie
*
*  Licensed under the Apache License, Version 2.0 (the "License");
*  you may not use this file except in compliance with the License.
*  You may obtain a copy of the License at
*
*  http://www.apache.org/licenses/LICENSE-2.0
*
*  Unless required by applicable law or agreed to in writing, software
*  distributed under the License is distributed on an "AS IS" BASIS,
*  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
*  See the License for the specific language governing permissions and
*  limitations under the License.
*/
package me.zhengjie.business.repository;

import me.zhengjie.business.domain.Hljg;
import me.zhengjie.business.domain.Qy;
import me.zhengjie.business.domain.vo.DqyVo;
import me.zhengjie.business.domain.vo.QyHomeJyVO;
import me.zhengjie.business.domain.vo.ZhgkQyVo;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
* @website https://el-admin.vip
* @author xiahongbo
* @date 2022-07-14
**/
public interface QyRepository extends JpaRepository<Qy, String>, JpaSpecificationExecutor<Qy> {

    Qy getByBisQyZhsqId(Long zhid);
    List<Qy> queryByBisQyZhsqId(Long zhid);

    Qy queryByBisQyZhsqIdAndBisGsglIszf(Long zhid, String iszf);

    @Query(value = "select * from ( " +
                    "select t.* from bis_qy t where t.bis_qy_zhsq_id=?1 order by t.lrsj asc " +
                    ") where rownum=1 ",
            nativeQuery = true)
    Qy queryFirstByZhid(Long zhid);

    // +
    //"AND if(?3 !='', (R1.BIS_ZHSQ_SNRY_XM like '%'||?3||'%' or R1.BIS_ZHSQ_SNRY_SFZ like '%'||?3||'%'or R1.BIS_ZHSQ_JHRDH like '%'||?3||'%' ), 1=1) "
    @Query(
        value = "SELECT " +
                "R1.BIS_ZHSQ_ID, " +
                "R1.BIS_ZHSQ_SNRY_XM, " +
                "R1.BIS_ZHSQ_SNRY_XB, " +
                "R1.BIS_ZHSQ_SNRY_SFZ, " +
                "R1.BIS_ZHSQ_SNDJ, " +
                "R1.BIS_ZHSQ_LXDH, " +
                "R1.BIS_ZHSQ_JHRXM, " +
                "R1.BIS_ZHSQ_JHRDH, " +
                "R1.BIS_ZHSQ_HLDZ, " +
                "R1.BIS_ZHSQ_NL, " +
                "R1.BIS_ZHSQ_ADL, " +
                "R1.BIS_ZHSQ_GX, " +
                "R2.BIS_QY_ZT, " +
                "TO_CHAR(R2.LRSJ, 'YYYY-MM-DD HH24:MI:SS'), " +
                "R1.BIS_ZHSQ_HLDZ_XXDZ " +
                "FROM BIS_ZHSQ R1, BIS_QY R2  " +
                "WHERE R1.BIS_ZHSQ_ID=R2.BIS_QY_ZHSQ_ID " +
                "AND R2.BIS_QY_HLJG_ID=?1 " +
                "AND R2.BIS_QY_ZT=?2 " +
                "AND R2.BIS_GSGL_ISZF=1 ",
        nativeQuery = true)
    List<Object> queryList(String hljgId, String zt, String blurry, Pageable pageable);


    @Query(value = "select new me.zhengjie.business.domain.vo.ZhgkQyVo( " +
            "t.bisZhsqId, " +
            "g.bisGsglQymc as bisHljgQymc, " +
            "s.bisGsglQymc as bisSbgsQymc, " +
            "t.bisZhsqSnryId, " +
            "t.bisZhsqSnryXm, " +
            "t.bisZhsqSnryXb, " +
            "t.bisZhsqSnrySfz, " +
            "t.bisZhsqSnryYbssqx, " +
            "t.bisZhsqDykkrq, " +
            "t.bisZhsqDyjsrq, " +
            "t.bisZhsqXsdyq, " +
            "t.bisZhsqAdl, " +
            "t.bisZhsqZt, " +
            "t.bisZhsqAdlPc, " +
            "t.bisZhsqSndj, " +
            "t.bisZhsqRybh, " +
            "t.bisZhsqNl, " +
            "t.bisZhsqLxdh, " +
            "t.bisZhsqHldzSsqx, " +
            "t.bisZhsqHldzXxdz, " +
            "t.bisZhsqHldz, " +
            "t.bisZhsqJhrxm, " +
            "t.bisZhsqJhrdh, " +
            "t.bisZhsqGx, " +
            "t.bisZhsqSqyy, " +
            "t.bisZhsqYbkh, " +
            "t.bisZhsqCbzt, " +
            "t.bisUserId, " +
            "t.bisSbgsId, " +
            "t.bisPggsId, " +
            "t.bisZhsqGrbh, " +
            "t.bisZhsqZhlb, " +
            "t.bisZhsqHljgBm, " +
            "t.lrr, " +
            "t.lrrxm, " +
            "t.lrbm, " +
            "t.lrbmmc, " +
            "t.lrsj, " +
            "t.bz," +
            "t2.bisQyQysj," +
            "t2.bisQyZt) " +
            "from Zhsq t " +
            "left join Gsgl g on t.bisZhsqHljgBm=g.bisGsglYljgdm " +
            "inner join Gsgl s on t.bisSbgsId=s.bisGsglId " +
            "inner join Qy t2 on t.bisZhsqId=t2.bisQyZhsqId and t2.bisGsglIszf='1' " +
            " where 1=1 " +
            " and (?1 IS NULL OR ?1 = '' OR t.bisZhsqSnryXm like '%'||?1||'%' OR t.bisZhsqSnrySfz like '%'||?1||'%' OR t.bisZhsqGrbh like '%'||?1||'%' OR t.bisZhsqLxdh like '%'||?1||'%' OR t.bisZhsqJhrdh like '%'||?1||'%' ) " +
            " and (?2 IS NULL OR ?2 = '' OR t.bisZhsqHldzSsqx = ?2 ) " +
            " and (?3 IS NULL OR ?3 = '' OR t2.bisQyHljgId = ?3  ) " +
            " and (?4 IS NULL OR ?4 = '' OR t2.bisQyZt = ?4 ) ")
    Page<ZhgkQyVo> queryList(String blurry, String area, String hilgId, String qyzt, Pageable pageable);


    @Query(
            value = "SELECT " +
                    "R1.BIS_ZHSQ_ID, " +
                    "R1.BIS_ZHSQ_SNRY_XM, " +
                    "R1.BIS_ZHSQ_SNRY_XB, " +
                    "R1.BIS_ZHSQ_SNRY_SFZ, " +
                    "R1.BIS_ZHSQ_SNDJ, " +
                    "R1.BIS_ZHSQ_LXDH, " +
                    "R1.BIS_ZHSQ_JHRXM, " +
                    "R1.BIS_ZHSQ_JHRDH, " +
                    "R1.BIS_ZHSQ_HLDZ, " +
                    "R1.BIS_ZHSQ_NL, " +
                    "R1.BIS_ZHSQ_ADL, " +
                    "R1.BIS_ZHSQ_GX, " +
                    "R2.BIS_QY_ZT, " +
                    "TO_CHAR(R2.LRSJ, 'YYYY-MM-DD HH24:MI:SS'), " +
                    "R2.BIS_GSGL_ISZF, " +
                    "R2.BIS_QY_ID " +
                    "FROM BIS_ZHSQ R1, BIS_QY R2  " +
                    "WHERE R1.BIS_ZHSQ_ID=R2.BIS_QY_ZHSQ_ID ",
            nativeQuery = true)
    List<Object> queryAllQyList(String hljgId, String zt, String blurry, Pageable pageable);

    //  +
    //                    "AND (R1.BIS_ZHSQ_SNRY_XM like '%'||?3||'%' or R1.BIS_ZHSQ_SNRY_SFZ like '%'||?3||'%'or R1.BIS_ZHSQ_JHRDH like '%'||?3||'%' ) "
    @Query(
            value = "SELECT " +
                    "R1.BIS_ZHSQ_ID, " +
                    "R1.BIS_ZHSQ_SNRY_XM, " +
                    "R1.BIS_ZHSQ_SNRY_XB, " +
                    "R1.BIS_ZHSQ_SNRY_SFZ, " +
                    "R1.BIS_ZHSQ_SNDJ, " +
                    "R1.BIS_ZHSQ_LXDH, " +
                    "R1.BIS_ZHSQ_JHRXM, " +
                    "R1.BIS_ZHSQ_JHRDH, " +
                    "R1.BIS_ZHSQ_HLDZ, " +
                    "R1.BIS_ZHSQ_NL, " +
                    "R1.BIS_ZHSQ_ADL, " +
                    "R1.BIS_ZHSQ_GX, " +
                    "R2.BIS_QY_MP_ZT, " +
                    "TO_CHAR(R2.LRSJ, 'YYYY-MM-DD HH24:MI:SS'), " +
                    "R2.BIS_QY_ID " +
                    "FROM BIS_ZHSQ R1, BIS_QY R2  " +
                    "WHERE R1.BIS_ZHSQ_ID=R2.BIS_QY_ZHSQ_ID " +
                    "AND R2.BIS_QY_HLJG_ID=?1 " +
                    "AND R2.BIS_QY_ZT=?2 ",
            nativeQuery = true)
    Page<DqyVo> queryMpList(String hljgId, String zt, String blurry, Pageable pageable);

    Page<Qy> queryByBisQyHljgIdAndBisQyZt(String bisQyHljgId, String bisQyZt, Pageable pageable);

    @Query(value = "SELECT " +
            "qy.* " +
            "FROM " +
            "BIS_QY qy " +
            "LEFT JOIN BIS_FPRY fp ON qy.BIS_QY_ZHSQ_ID = fp.BIS_FPRY_ZHSQ_ID  " +
            "LEFT JOIN bis_zhsq z ON qy.BIS_QY_ZHSQ_ID = z.bis_zhsq_id  " +
            "WHERE " +
            "qy.BIS_QY_ZT = ?1 " +
            "AND fp.BIS_FPRY_RYGL_ID = ?2 " +
            "AND (z.bis_zhsq_snry_xm like '%'||?3||'%' or z.bis_zhsq_snry_sfz like '%'||?3||'%') " +
            "AND fp.BIS_FPRY_ZT = '0' ORDER BY qy.lrsj DESC",nativeQuery = true,
            countProjection = "1")
    Page<Qy> queryAllByPgUserId(String zt, Long id, String blurry, Pageable pageable);


    @Modifying
    @Query(value = "UPDATE BIS_QY SET BIS_GSGL_ISZF=?1 WHERE BIS_QY_ZHSQ_ID=?2",nativeQuery = true)
    void updateBisGsglIszfByBisQyZhsqId(String bisGsglIszf,Long bisQyZhsqId);

    @Query(value = "select new me.zhengjie.business.domain.vo.QyHomeJyVO( " +
            "tt.bisQyId,  " +
            "tt.bisQyHljgId,  " +
            "tt.bisQyZhsqId,  " +
            "tt.bisQyQysj,  " +
            "tt.bisQyZt,  " +
            "z.bisZhsqSnryXm,  " +
            "z.bisZhsqSnryXb,  " +
            "z.bisZhsqNl,  " +
            "z.bisZhsqSnrySfz,  " +
            "z.bisZhsqSnryYbssqx,  " +
            "z.bisZhsqDykkrq,  " +
            "z.bisZhsqDyjsrq,  " +
            "z.bisZhsqZt,  " +
            "z.bisZhsqHldz,  " +
            "z.bisZhsqJhrxm,  " +
            "z.bisZhsqJhrdh,  " +
            "z.bisZhsqGx,  " +
            "z.bisZhsqCblx," +
            "z.bisZhsqNl," +
            "g.bisGsglQymc)" +
            "from Qy tt" +
            " left join Zhsq z on tt.bisQyZhsqId = z.bisZhsqId" +
            " INNER JOIN Gsgl g on tt.bisQyHljgId = g.bisGsglId" +
            " where" +
            "  tt.bisGsglIszf = '1' " +
            "  and (?1 IS NULL OR ?1 = '' OR z.bisZhsqSnryXm = ?1 OR z.bisZhsqSnrySfz like '%'||?1||'%') "+
            "  and (?3 IS NULL OR ?3 = '' OR tt.bisQyHljgId = ?3) "+
            "  and tt.bisQyZt in (?2) " ,
            countQuery ="select count(*)  " +
                    "from Qy tt" +
                    " left join Zhsq z on tt.bisQyZhsqId = z.bisZhsqId" +
                    " INNER JOIN Gsgl g on tt.bisQyHljgId = g.bisGsglId" +
                    " where " +
                    "  tt.bisGsglIszf = '1' " +
                    "  and (?1 IS NULL OR ?1 = '' OR z.bisZhsqSnryXm = ?1 OR z.bisZhsqSnrySfz like '%'||?1||'%') "+
                    "  and (?3 IS NULL OR ?3 = '' OR tt.bisQyHljgId = ?3) "+
                    "  and tt.bisQyZt in (?2) "
    )
    Page<QyHomeJyVO> queryQyHomeJYList(String xm, List<String> qyzt, String hljg, Pageable pageable);

    @Query(value = "select b.bis_gsgl_qymc " +
            "  from BIS_QY t " +
            "  left join bis_gsgl b " +
            "    on t.bis_qy_hljg_id = b.bis_gsgl_id " +
            " where t.bis_qy_zhsq_id = ?1 " +
            "   and t.bis_gsgl_iszf = 1  ",nativeQuery = true)
    String findByZhId(Long bisZhsqId);
}